/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package System.model;

import conexao.DAOException;
import conexao.ConectaBanco;
import java.sql.Connection;
import java.sql.PreparedStatement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Administrador
 */
public class PlaceDAO {


     private Connection conn;

    public PlaceDAO() throws DAOException{
        try{
            this.conn = ConectaBanco.getConnection();
        }catch(Exception e){
            throw new DAOException("Erro de conexão: " + ":\n" + e.getMessage());
        }
    }


    public void salvar(Place u) throws DAOException {
        PreparedStatement ps = null;
        Connection connL = null;
        if (u == null){
            throw new DAOException("O objeto Produto passado não pode ser nulo.");
        }

        try{
            String SQL = "INSERT INTO Place (ID, NAME, ID_ADMIN) values (?,?,?,?)";
           connL = this.conn;

           ps = connL.prepareStatement(SQL);

           ps.setInt(1, u.getId    ());
           ps.setString(2, u.getNome());
           ps.setInt(3, u.getId_admin());
            

            ps.executeUpdate();

        }catch(SQLException sqle){
           throw new DAOException("Erro ao inserir um novo Usuário--->> " + sqle);
        }
        finally{
           ConectaBanco.closeConnection(connL,ps);
        }
    }
        public ArrayList<Place> todosPlaces() throws DAOException {
        PreparedStatement ps = null;
        Connection connL = null;
        ResultSet rs = null;

        try{
            String SQL = "SELECT * FROM Place;";
            connL = this.conn;

            ps = connL.prepareStatement(SQL);
            rs = ps.executeQuery();

            ArrayList places = new ArrayList<Place>();

            while( rs.next()){
              int codigo       = rs.getInt("ID_PLACE");
              String nome = rs.getString("NAME");
              //String situacao    = rs.getString("login");



              places.add(new Place(codigo, nome));
            }

            return places;

        }catch(SQLException sqle){
           throw new DAOException("Erro ao consultar cliente " + sqle);
        }
        finally{
           ConectaBanco.closeConnection(connL,ps);
        }
    }

       public Place getPlace(int id) throws DAOException {
        PreparedStatement ps = null;
        Connection connL = null;
        ResultSet rs = null;
        ResultSet rsServ = null;
        ResultSet rsServVal = null;


        try{
            String SQL = "SELECT * FROM Place where ID_PLACE=" + Integer.toString(id);
            connL = this.conn;

            ps = connL.prepareStatement(SQL);
            rs = ps.executeQuery();


            Place place = new Place();
            if (!rs.next())  throw new DAOException("Place inexistente");
            rs.previous();
            while( rs.next()){
              int codigo       = rs.getInt("ID_PLACE");
              String nome = rs.getString("NAME");
              //String situacao    = rs.getString("login");

            place.setId(codigo);
            place.setNome(nome);

            /////////////////////////
            ////SERVICOS DO LUGAR
            /////////////////////////

            String SQLpref = " select Service.*, Service_Place.* from Service "
                    + "left join Service_Place on (Service_Place.SERVICE_ID_SERVICE= Service.ID_SERVICE)"
                    + "where Service_Place.PLACE_ID_PLACE= "
                    + Integer.toString(id);
            connL = this.conn;
            ps = connL.prepareStatement(SQLpref);
            rsServ = ps.executeQuery();

            //if (!rsServ.next())  throw new DAOException("Place não contem servicos");
            //rs.previous();

            while( rsServ.next()){
              int codigoServ       = rsServ.getInt("ID_SERVICE ");
              String nomeServ = rsServ.getString("NAME");

              ServiceValue serCurrent = new ServiceValue(rsServ.getInt("DEFAULT_VALUE"), "");
              ServiceValue serDefault = new ServiceValue(rsServ.getInt("DEFAULT_VALUE"), "");

              Service serv = new Service(codigoServ, nomeServ, serCurrent, serDefault );

              //////////////////////
              //VALORES DOS SERVICOS
              //////////////////////
              String SQLServ = " select Service_Value.* from Service_Value where ID_SERVICE=" + Integer.toString(codigoServ);
              connL = this.conn;

                ps = connL.prepareStatement(SQLServ);
                rsServVal = ps.executeQuery();
                while( rsServVal.next()){
                  String nomeValue = rsServVal.getString("STRING_VALUE");
                  int intValue = rsServVal.getInt("INT_VALUE");

                  //String situacao    = rs.getString("login");
                  ServiceValue serviceValue = new ServiceValue( intValue, nomeValue);
                  serv.addValues(serviceValue);

                }

              place.addService(serv);

            }
          }

            return place;

        }catch(SQLException sqle){
           throw new DAOException("Erro ao consultar cliente " + sqle);
        }
        finally{
           ConectaBanco.closeConnection(connL,ps);
        }
    }
    


}
